//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.aidong.dao.wills;

import com.aidong.dao.wills.BaseDao;
import com.aidong.model.wills.ReportSalesByTraCode;
import com.aidong.model.wills.TbModel;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.springframework.stereotype.Repository;

@Repository
public class ReportSalesByTraCodeDao extends BaseDao<ReportSalesByTraCode, Integer> {
    public ReportSalesByTraCodeDao() {
    }

    public List<TbModel> getTbDataAll() {
        String sql = "select a.code,b.CodeName, sum(a.Amount) as Amount , max(c.monthAmount) as monthAmount, max(d.yearAmount) as yearAmount  from ReportSalesByTraCode a  \nleft join DicTranCode b on a.code = b.TranCode  \nleft join (select a.code,sum(a.amount) as monthAmount from ReportSalesByTraCode a \nwhere a.ShopCode<>\'Test\' and Convert(varchar(6),a.reportdate,112) = convert(nvarchar(6),dateadd(day, -1, Getdate()),112) \ngroup by a.code ) c on c.code = a.code \nleft join (select a.code,sum(a.amount) as yearAmount from ReportSalesByTraCode a \nwhere a.ShopCode<>\'Test\' and Convert(varchar(4),a.reportdate,112) = convert(nvarchar(4),dateadd(day, -1, Getdate()),112) \ngroup by a.code ) d on d.code = a.code \nwhere a.ShopCode<>\'Test\' and Convert(varchar(10),a.reportdate,111) = Convert(varchar(10),dateadd(day, -1, Getdate()),111) \ngroup by a.code, b.CodeName \norder by a.code \n";
        return this.queryListBySql(TbModel.class, sql, new Object[0]);
    }

    public List<TbModel> getTbDataByShopCode(String shopCode) {
        String sql = "select a.code,b.CodeName, sum(a.Amount) as Amount , max(c.monthAmount) as monthAmount, max(d.yearAmount) as yearAmount  from ReportSalesByTraCode a  \nleft join DicTranCode b on a.code = b.TranCode  \nleft join (select a.code,sum(a.amount) as monthAmount from ReportSalesByTraCode a \nwhere Convert(varchar(6),a.reportdate,112) = convert(nvarchar(6),dateadd(day, -1, Getdate()),112) \nand a.ShopCode = :p1 \ngroup by a.code ) c on c.code = a.code \nleft join (select a.code,sum(a.amount) as yearAmount from ReportSalesByTraCode a \nwhere Convert(varchar(4),a.reportdate,112) = convert(nvarchar(4),dateadd(day, -1, Getdate()),112) \nand a.ShopCode = :p2 \ngroup by a.code ) d on d.code = a.code \nwhere Convert(varchar(10),a.reportdate,111) = Convert(varchar(10),dateadd(day, -1, Getdate()),111) \nand a.ShopCode = :p3 \ngroup by a.code, b.CodeName \norder by a.code \n";
        return this.queryListBySql(TbModel.class, sql, new Object[]{shopCode, shopCode, shopCode});
    }

    public List<TbModel> getTbDataAllByDay(String serDate) {
        String sql = "select a.code,b.CodeName, sum(a.Amount) as Amount , max(c.monthAmount) as monthAmount, max(d.yearAmount) as yearAmount  from ReportSalesByTraCode a  \nleft join DicTranCode b on a.code = b.TranCode  \nleft join (select a.code,sum(a.amount) as monthAmount from ReportSalesByTraCode a \nwhere a.ShopCode<>\'Test\' and Convert(varchar(6),a.reportdate,112) = convert(nvarchar(6),convert(DATETIME, :p1, 121),112) \ngroup by a.code ) c on c.code = a.code \nleft join (select a.code,sum(a.amount) as yearAmount from ReportSalesByTraCode a \nwhere a.ShopCode<>\'Test\' and Convert(varchar(4),a.reportdate,112) = convert(nvarchar(4),convert(DATETIME, :p2, 121),112) \ngroup by a.code ) d on d.code = a.code \nwhere a.ShopCode<>\'Test\' and Convert(varchar(10),a.reportdate,111) = Convert(varchar(10),convert(DATETIME, :p3, 121),111) \ngroup by a.code, b.CodeName \norder by a.code \n";
        return this.queryListBySql(TbModel.class, sql, new Object[]{serDate, serDate, serDate});
    }

    public List<TbModel> getTbDataByShopCodeAndDay(String shopCode, String serDate) {
        String sql = "select a.code,b.CodeName, sum(a.Amount) as Amount , max(c.monthAmount) as monthAmount, max(d.yearAmount) as yearAmount  from ReportSalesByTraCode a  \nleft join DicTranCode b on a.code = b.TranCode  \nleft join (select a.code,sum(a.amount) as monthAmount from ReportSalesByTraCode a \nwhere Convert(varchar(6),a.reportdate,112) = convert(nvarchar(6),convert(DATETIME, :p4, 121),112) \nand a.ShopCode = :p1 \ngroup by a.code ) c on c.code = a.code \nleft join (select a.code,sum(a.amount) as yearAmount from ReportSalesByTraCode a \nwhere Convert(varchar(4),a.reportdate,112) = convert(nvarchar(4),convert(DATETIME, :p5, 121),112) \nand a.ShopCode = :p2 \ngroup by a.code ) d on d.code = a.code \nwhere Convert(varchar(10),a.reportdate,111) = Convert(varchar(10),convert(DATETIME, :p6, 121),111) \nand a.ShopCode = :p3 \ngroup by a.code, b.CodeName \norder by a.code \n";
        return this.queryListBySql(TbModel.class, sql, new Object[]{shopCode, shopCode, shopCode, serDate, serDate, serDate});
    }

    public List<TbModel> getTbDataByDay(Date date, String shopCode) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        Date dt1 = cal.getTime();
        cal.add(5, 1);
        Date dt2 = cal.getTime();
        cal.add(5, -1);
        cal.set(5, 1);
        Date dt3 = cal.getTime();
        cal.add(2, 1);
        Date dt4 = cal.getTime();
        cal.add(2, -1);
        cal.set(2, 0);
        Date dt5 = cal.getTime();
        cal.add(1, 1);
        Date dt6 = cal.getTime();
        String op = "=";
        if(shopCode == null || shopCode.length() == 0) {
            op = "<>";
            shopCode = "Test";
        }

        String sql = "select tranCode code, CodeName, sum(Amount) Amount, sum(monthAmount) monthAmount, sum(yearAmount) yearAmount from (\nselect code, Amount,0 monthAmount,0 yearAmount from ReportSalesByTraCode where shopCode" + op + ":p1 and reportDate>=:p2 and reportDate<:p3\n" + "union all select code, 0, Amount, 0 from ReportSalesByTraCode where shopCode" + op + ":p4 and reportDate>=:p5 and reportDate<:p6\n" + "union all select code, 0, 0, Amount from ReportSalesByTraCode where shopCode" + op + ":p7 and reportDate>=:p8 and reportDate<:p9\n" + ") r right join DicTranCode t on r.code = t.TranCode\n" + "group by tranCode, CodeName order by code";
        return this.queryListBySql(TbModel.class, sql, new Object[]{shopCode, dt1, dt2, shopCode, dt3, dt4, shopCode, dt5, dt6});
    }

    public void doReportMember(Date dateFrom, Date dateTo) {
        String sql = "{call sp_ReportMember(:p1, :p2)}";
        this.updateBySql(sql, new Object[]{dateFrom, dateTo});
    }

    public void doReportPT(Date dateFrom, Date dateTo) {
        String sql = "{call sp_ReportPT(:p1, :p2)}";
        this.updateBySql(sql, new Object[]{dateFrom, dateTo});
    }

    public void doReportDeposit(Date dateFrom, Date dateTo) {
        String sql = "{call sp_ReportDeposit(:p1, :p2)}";
        this.updateBySql(sql, new Object[]{dateFrom, dateTo});
    }

    public void doTraCodeReport(Date dateFrom, Date dateTo) {
        String sql = "{call sp_TraCodeReport(:p1, :p2)}";
        this.updateBySql(sql, new Object[]{dateFrom, dateTo});
    }
}
